<?php
	//daily process
	include("../connect/connect_dsccmis.php");
	//สร้างตารางรายละเอียดเหตุการณ์และำพิกัด
	$sql = "drop table if exists gis_event;";
	$rs=mysql_query($sql);
	
    $sql="CREATE TABLE gis_event AS 
SELECT e1.evnid,e1.evndte,e1.evntme,LEFT(e1.evnplcidgen,2) AS prov,
CONCAT(e1.evnplcidgen,e1.evnvllnogen) vill_code,'' AS vill_name,
cdscc,e2.mis_evntypid,e1.evnnme,e1.evndes,e1.plcnmegen,e1.tarx,e1.tary,
NULL AS lat,NULL AS lng,e1.upddte FROM dscc.viewevent e1 LEFT JOIN  
(SELECT e1.evnid,MIN(e3.mis_evntypid) AS mis_evntypid FROM dscc.viewevent e1 
LEFT JOIN dscc.evnrelevntyp e2 ON e1.evnid=e2.evnid
 LEFT JOIN convert_evntyp e3 ON e2.evntypid=e3.evntypid GROUP BY e1.evnid)
 e2 ON e1.evnid=e2.evnid LEFT JOIN dscc.TempEventAndCaseOfEvent e3
ON e1.evnid=e3.evnid";

	mysql_query($sql);
	
	$sql = "ALTER TABLE  gis_event CHANGE  lat  lat DOUBLE NULL DEFAULT NULL ,
CHANGE  lng lng DOUBLE NULL DEFAULT NULL,CHANGE  vill_name vill_name varchar(255) NULL DEFAULT NULL;";
	mysql_query($sql);		
		// create index
	$sql="CREATE INDEX evnid_index USING BTREE ON gis_event(evnid)";
	mysql_query($sql);
	//mysql_close($c);
	
//delete all kml file of yesterday ---------------------------
//$tempY=date("Y");
//if($tempY<"2500"){
//	$tempY=(int)$tempY+543;
//}
//$datetime_yes= $tempY.date("md",strtotime("-1 day"));
//echo "datetime_yes=".$datetime_yes;
//foreach(glob('../gis_event'.$datetime_yes.'*.kml') as $file){
//foreach(glob('../kml/gis_event*.kml') as $file){
//     unlink($file);
//}
//-------------------------------------------------------------
echo "<br>complete create gis_event";
?>